import datetime
import pandas as pd
from db_conn import pd_conn_zfw


# 全国当月用户
def current_month_users(startTime, endTime):
    sql = """
        select distinct f_user_id
        from t_order o force index (order_date_index)
        where o.f_order_date >= '{_startTime}' and o.f_order_date < '{_endTime}'
        and (o.f_status in (50, 60) OR ( o.f_status = 15 AND o.f_pay_type IN (
            SELECT DISTINCT f_pay_type FROM t_order_income_config WHERE f_status = 1 AND f_is_refund = 0
        ) ) )
        AND f_is_insider = 0 AND f_service_id = 2002
    """.format(_startTime=startTime, _endTime=endTime)
    return pd_conn_zfw(sql)


def country_retain_rate(count_start_time, startTime, endTime):

    count_end_time = (datetime.datetime.strptime(count_start_time, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
            day=1).strftime("%Y-%m-%d")

    # 当月洗车用户
    df = current_month_users(count_start_time, count_end_time)
    df_rate = pd.DataFrame([], columns=['月份', '新用户', '留存数', '留存率'])
    i = 1

    while startTime != endTime:
        reduceOneDay = (datetime.datetime.strptime(startTime, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
            day=1).strftime("%Y-%m-%d")
        df_new = pd.read_csv(r'D:\Store\Python-Download\全国月新用户\全国月新用户_{}.csv'.format(startTime[:7]))
        df_retain = df.merge(df_new, how="inner", on='f_user_id')
        ori = df_new.shape[0]
        retain = df_retain.shape[0]
        retain_rate = retain / ori
        df_rate.loc[i] = [startTime, ori, retain, retain_rate]
        df_rate.to_excel(r"D:\Store\Python-Download\全国复购率{}.xlsx".format(count_start_time[:7]), index=False)
        print(startTime, ori, retain, retain_rate)
        startTime = reduceOneDay
        i += 1


if __name__ == '__main__':

    count_start_time = '2021-07-01'     # 每月更改

    startTime = '2018-01-01'    # 起始计算日期基本不用变
    endTime = '2022-01-01'  # 21年数据不用改

    country_retain_rate(count_start_time, startTime, endTime)

